#!/bin/bash

APP=edu
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

#lv 1
dwd_trade_cart_full="
insert overwrite table ${APP}.dwd_trade_cart_full partition(dt='$do_date')
select
    id,
    user_id,
    course_id,
    course_name,
    cart_price
from ${APP}.ods_cart_info_full
where dt='$do_date'
and deleted='0'
and sold='0';
"
#lv 2
dwd_interaction_favor_add_inc="
insert overwrite table ${APP}.dwd_interaction_favor_add_inc partition(dt='$do_date')
select
    data.id,
    data.user_id,
    data.course_id,
    date_format(data.create_time,'yyyy-MM-dd') date_id,
    data.create_time
from ${APP}.ods_favor_info_inc
where dt='$do_date'
and type = 'insert'; 
"

#lv 3
dwd_interaction_review_inc="
insert overwrite table ${APP}.dwd_interaction_review_inc partition(dt='$do_date')
select
    data.id,
    data.user_id,
    data.course_id,
    date_format(data.create_time,'yyyy-MM-dd'),
    data.create_time,
    data.review_txt,
    data.review_stars
from ${APP}.ods_review_info_inc
where dt='$do_date'
and type='insert'; 
"
#lv 4
dwd_interaction_comment_inc="
insert overwrite table ${APP}.dwd_interaction_comment_inc partition(dt='$do_date')
select
    data.id,
    data.user_id,
    data.chapter_id,
    data.course_id,
    date_format(data.create_time,'yyyy-MM-dd'),
    data.create_time,
    data.comment_txt
from ${APP}.ods_comment_info_inc
where dt='$do_date'
and type='insert';
"

#zhou 1
dwd_trade_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition(dt='$do_date')
select
    ci.id,
    ci.user_id,
    ci.course_id,
    ci.date_id,
    ci.create_time
from
(
    select
        data.id id,
        data.user_id user_id,
        data.course_id course_id,
        date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd') date_id,
        date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd HH:mm:ss') create_time
    from ${APP}.ods_cart_info_inc
    where dt='$do_date'
    and type='insert'
)ci; 
"

#zhou 2
dwd_trade_order_detail_inc="
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition (dt='$do_date')
select
    od.id,
    od.order_id,
    oi.user_id,
    od.course_id,
    oi.province_id,
    date_format(od.create_time, 'yyyy-MM-dd') date_id,
    od.create_time,
    od.split_origin_amount,
    od.split_coupon_reduce,
    od.split_final_amount
from
(
    select
        data.id id,
        data.order_id order_id,
        data.course_id course_id,
        data.create_time create_time,
        data.origin_amount split_origin_amount,
        data.coupon_reduce split_coupon_reduce,
        data.final_amount split_final_amount
    from ${APP}.ods_order_detail_inc
    where dt = '$do_date'
    and type = 'insert'
) od
left join
(
    select
        data.id id,
        data.user_id user_id,
        data.province_id province_id
    from ${APP}.ods_order_info_inc
    where dt = '$do_date'
    and type = 'insert'
) oi
on od.order_id = oi.id; 
"

#zhou 3
dwd_trade_cancel_detail_inc="
insert overwrite table ${APP}.dwd_trade_cancel_detail_inc partition (dt='$do_date')
select
    od.id,
    od.order_id,
    oi.user_id,
    od.course_id,
    oi.province_id,
    date_format(oi.cancel_time, 'yyyy-MM-dd') date_id,
    oi.cancel_time,
    od.split_origin_amount,
    od.split_coupon_reduce,
    od.split_final_amount
from
(
    select
        data.id id,
        data.order_id order_id,
        data.course_id course_id,
        data.origin_amount split_origin_amount,
        data.coupon_reduce split_coupon_reduce,
        data.final_amount split_final_amount
    from ${APP}.ods_order_detail_inc
    where (dt='$do_date' or dt=date_add('$do_date',-1))
    and (type = 'insert' or type= 'bootstrap-insert')
) od
join
(
    select
        data.id id,
        data.user_id user_id,
        data.province_id province_id,
        data.update_time cancel_time
    from ${APP}.ods_order_info_inc
    where dt = 'do_date'
    and type = 'update'
    and data.order_status='1003'
    and array_contains(map_keys(old),'order_status')
) oi
on order_id = oi.id;
"
#zhou 4
dwd_trade_pay_detail_suc_inc="
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition (dt='$do_date')
select
    od.id,
    od.order_id,
    user_id,
    course_id,
    province_id,
    payment_type,
    date_format(callback_time,'yyyy-MM-dd') date_id,
    callback_time,
    split_origin_amount,
    split_coupon_reduce,
    split_final_amount
from
(
    select
        data.id id,
        data.order_id order_id,
        data.course_id course_id,
        data.origin_amount split_origin_amount,
        data.coupon_reduce split_coupon_reduce,
        data.final_amount split_final_amount
    from ${APP}.ods_order_detail_inc
    where (dt = '$do_date' or dt = date_add('$do_date',-1))
    and (type = 'insert' or type = 'bootstrap-insert')
) od
join
(
    select
        data.order_id order_id,
        data.payment_type payment_type,
        data.callback_time callback_time
    from ${APP}.ods_payment_info_inc
    where dt='$do_date'
    and type='insert'
    and data.payment_status ='1602'
) pi
on od.order_id=pi.order_id
left join
(
    select
        data.id id,
        data.user_id user_id,
        data.province_id province_id
    from ${APP}.ods_order_info_inc
    where (dt = '$do_date' or dt = date_add('$do_date',-1))
    and (type = 'insert' or type = 'bootstrap-insert')
) oi
on od.order_id = oi.id;
" 


#wxc 1
dwd_traffic_display_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_display_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    source,
sid,
    user_id,
    version_code,
    during_time,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') display_time,
    display_type,
    display_item,
    display_item_type,
    display_order,
    display_pos_id
from
(
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.sc source,
        common.sid,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        display.display_type,
        display.item display_item,
        display.item_type display_item_type,
        display.\`order\` display_order,
        display.pos_id display_pos_id,
        ts
    from ${APP}.ods_log_inc lateral view explode(displays) tmp as display
    where dt='$do_date'
    and displays is not null
)log;
"
#wxc 2
dwd_traffic_error_inc="
set hive.cbo.enable=false;
set hive.execution.engine=mr;
set hive.auto.convert.join=false;
insert overwrite table ${APP}.dwd_traffic_error_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    source,
sid,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    entry,
    first_open,
    loading_time,
    open_ad_id,
    open_ad_ms,
    open_ad_skip_ms,
    actions,
    displays,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') error_time,
    error_code,
    error_msg
from
(
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.sc source,
        common.sid,
        common.uid user_id,
        common.vc version_code,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        \`start\`.entry,
        \`start\`.first_open,
        \`start\`.loading_time,
        \`start\`.open_ad_id,
        \`start\`.open_ad_ms,
        \`start\`.open_ad_skip_ms,
        actions,
        displays,
        err.error_code,
        err.msg error_msg,
        ts
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and err is not null
)log;
set hive.execution.engine=spark;
"
#wxc 3
dwd_user_register_inc="
insert overwrite table ${APP}.dwd_user_register_inc partition(dt='$do_date')
select
    ui.user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time
from
(
    select
        data.id user_id,
        data.create_time
    from ${APP}.ods_user_info_inc
    where dt='$do_date'
    and type='insert'
)ui;
"
#wxc 4
dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition(dt='$do_date')
select
    user_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
(
    select
        user_id,
        channel,
        province_id,
        version_code,
        mid_id,
        brand,
        model,
        operate_system,
        ts
    from
    (
        select
            user_id,
            channel,
            province_id,
            version_code,
            mid_id,
            brand,
            model,
            operate_system,
            ts,
            row_number() over (partition by sid order by ts) rn
        from
            (
                select
                    common.uid user_id,
                    common.ch channel,
                    common.ar province_id,
                    common.vc version_code,
                    common.mid mid_id,
                    common.ba brand,
                    common.md model,
                    common.os operate_system,
                    ts,
                    common.sid sid
                from ${APP}.ods_log_inc
                where dt='$do_date'
             )t1
        where user_id is not null
    )t2
    where rn=1
)t3;
"
#wj 1
dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt='$do_date')
select province_id,
       brand,
       channel,
       is_new,
       model,
       mid_id,
       operate_system,
       source,
       sid,
       user_id,
       version_code,
       page_item,
       page_item_type,
       last_page_id,
       page_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd')                           date_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss')                  view_time,
       during_time
from (
         select common.ar                               province_id,
                common.ba                               brand,
                common.ch                               channel,
                common.is_new                           is_new,
                common.md                               model,
                common.mid                              mid_id,
                common.os                               operate_system,
                common.sc                               source,
                common.sid                              sid,
                common.uid                              user_id,
                common.vc                               version_code,
                page.during_time,
                page.item                               page_item,
                page.item_type                          page_item_type,
                page.last_page_id,
                page.page_id,
                ts
         from ${APP}.ods_log_inc
         where dt = '$do_date'
           and page is not null
) log;
"
#wj 2
dwd_traffic_start_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_start_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    source,
    sid,
    user_id,
    version_code,
    entry,
    open_ad_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') start_time,
    loading_time,
    open_ad_ms,
    open_ad_skip_ms
from (
         select common.ar  province_id,
                common.ba  brand,
                common.ch  channel,
                common.is_new,
                common.md  model,
                common.mid mid_id,
                common.os  operate_system,
                common.sc  source,
                common.sid sid,
                common.uid user_id,
                common.vc  version_code,
                \`start\`.entry,
                \`start\`.loading_time,
                \`start\`.open_ad_id,
                \`start\`.open_ad_ms,
                \`start\`.open_ad_skip_ms,
                ts
         from ${APP}.ods_log_inc
         where dt = '$do_date'
           and \`start\` is not null
     )log;
"
#wj 3
dwd_traffic_action_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_action_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    source,
    sid,
    user_id,
    version_code,
    during_time,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    action_id,
    action_item,
    action_item_type,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time
from
(
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.sc source,
        common.sid sid,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        action.action_id,
        action.item action_item,
        action.item_type action_item_type,
        action.ts
    from ${APP}.ods_log_inc lateral view explode(actions) tmp as action
    where dt='$do_date'
    and actions is not null
)log;
"
#wj 4
dwd_interaction_test_exam_inc="
insert overwrite table ${APP}.dwd_interaction_test_exam_inc partition(dt='$do_date')
select
        te.id,
        paper_id,
        user_id,
        course_id,
        score,
        duration_sec
from (
         select data.id id,
                data.paper_id paper_id,
                data.user_id user_id,
                data.score score,
                data.duration_sec duration_sec
         from ${APP}.ods_test_exam_inc
         where dt = '$do_date'
           and type = 'insert'
     )te join
    (    select
             id,
             course_id
         from ${APP}.ods_test_paper_full
         where dt = '$do_date'
    )tp  on te.paper_id=tp.id;
"
#wj 5
dwd_interaction_watch_video_inc="
insert overwrite table ${APP}.dwd_interaction_watch_video_inc partition(dt='$do_date')
select
        cp.id,
        user_id,
        cp.course_id,
        cp.chapter_id,
        vi.id video_id,
        create_time,
        update_time,
        during_sec,
        position_sec
from
(
    select data.id      id,
           data.user_id user_id,
           data.course_id course_id,
           data.create_time,
           data.update_time,
           data.chapter_id chapter_id,
           data.position_sec position_sec,
           dt
    from ${APP}.ods_user_chapter_process_inc
    where dt = '$do_date'
      and type = 'insert'
)cp
left join
(
    select id,
           during_sec,
           course_id,
           chapter_id
    from ${APP}.ods_video_info_full
    where dt = '$do_date'
)vi on cp.course_id=vi.course_id
    and cp.chapter_id=vi.chapter_id;
"

dwd_traffic_log_watch_video_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_log_watch_video_inc partition(dt='$do_date')
select
    appvideo.id,
    user_id,
    video_id,
    chapter_id,
    course_id,
    during_sec,
    play_sec,
    position_sec,
    date_id,
    watch_time
from
(
    select
        ts id,
        common.uid user_id,
        appvideo.video_id,
        appvideo.play_sec,
        appvideo.position_sec,
        date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
        date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') watch_time
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and appvideo is not null
) appvideo
left join
(
    select
        id,
        during_sec,
        chapter_id,
        course_id
    from ${APP}.ods_video_info_full
    where dt='$do_date'
) video
on appvideo.video_id=video.id;
"
dwd_trade_audition_order_full="

with course_info as
(

    select ci.id course_id,
           course_name,
           subject_id,
           subject_name,
           category_id,
           category_name
    from (
             select id,
                    course_name,
                    subject_id
             from ${APP}.ods_course_info_full
             where dt = '$do_date'
         ) ci
             join
         (
             select id,
                    subject_name,
                    category_id
             from ${APP}.ods_base_subject_info_full
             where dt = '$do_date'
         ) si
         on ci.subject_id = si.id
             join
         (
             select id,
                    category_name
             from ${APP}.ods_base_category_info_full
             where dt = '$do_date'
         ) cai
         on si.category_id = cai.id

),
auti as (
    select au.user_id user_id,
           au.course_id course_id,
           au.audition_time audition_time,
           order_time,
           if(order_time is not null or datediff('$do_date', audition_time) > 7, '$do_date', '9999-12-31') dt
    from (
             select user_id,
                    course_id,
                    min(create_time)                     audition_time
             from (
                      select data.user_id   user_id,
                             data.course_id course_id,
                             data.create_time
                      from ${APP}.ods_user_chapter_process_inc
                      where dt = '$do_date'
                        and type = 'insert'
                  ) cp
             group by user_id, course_id
             union
             select
                user_id,
                course_id,
                audition_time
             from ${APP}.dwd_trade_audition_order_full
         ) au
             left join
         (
             select data.course_id,
                    data.user_id,
                    data.create_time order_time
             from ${APP}.ods_order_detail_inc
             where dt = '$do_date'
               and type = 'insert'
         ) od
         on au.course_id = od.course_id
             and au.user_id = od.user_id
)
insert overwrite table ${APP}.dwd_trade_audition_order_full partition (dt)
select
    auti.user_id,
    auti.course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name,
    audition_time,
    order_time,
    dt
from
auti
join course_info
on auti.course_id = course_info.course_id;
"
case $1 in
   "dwd_trade_cart_full" )
        hive -e "$dwd_trade_cart_full"
    ;;
    "dwd_interaction_favor_add_inc" )
        hive -e "$dwd_interaction_favor_add_inc"
    ;;
    "dwd_ interaction_review_inc" )
        hive -e "$dwd_ interaction_review_inc"
    ;;
    "dwd_interaction_comment_inc" )
        hive -e "$dwd_interaction_comment_inc"
    ;;
    "dwd_trade_cart_add_inc" )
        hive -e "$dwd_trade_cart_add_inc"
    ;;
    "dwd_trade_order_detail_inc" )
        hive -e "$dwd_trade_order_detail_inc"
    ;;
    "dwd_trade_cancel_detail_inc" )
        hive -e "$dwd_trade_cancel_detail_inc"
    ;;
    "dwd_trade_pay_detail_suc_inc" )
        hive -e "$dwd_trade_pay_detail_suc_inc"
    ;;
    "dwd_traffic_display_inc" )
        hive -e "$dwd_traffic_display_inc"
    ;;
    "dwd_traffic_error_inc" )
        hive -e "$dwd_traffic_error_inc"
    ;;
    "dwd_user_register_inc" )
        hive -e "$dwd_user_register_inc"
    ;;
    "dwd_user_login_inc" )
        hive -e "$dwd_user_login_inc"
    ;;
    "dwd_traffic_page_view_inc" )
        hive -e "$dwd_traffic_page_view_inc"
    ;;
    "dwd_traffic_start_inc" )
        hive -e "$dwd_traffic_start_inc"
    ;;
    "dwd_traffic_action_inc" )
        hive -e "$dwd_traffic_action_inc"
    ;;
    "dwd_interaction_test_exam_inc" )
        hive -e "$dwd_interaction_test_exam_inc"
    ;;
    "dwd_interaction_watch_video_inc" )
        hive -e "$dwd_interaction_watch_video_inc"
    ;;
    "dwd_traffic_log_watch_video_inc" )
        hive -e "$dwd_traffic_log_watch_video_inc"
    ;;
    "dwd_trade_audition_order_full" )
        hive -e "$dwd_trade_audition_order_full"
    ;;
    "all" )
        hive -e "$dwd_trade_cart_full$dwd_interaction_favor_add_inc$dwd_interaction_review_inc$dwd_interaction_comment_inc$dwd_trade_cart_add_inc$dwd_trade_cancel_detail_inc$dwd_trade_order_detail_inc$dwd_trade_pay_detail_suc_inc$dwd_traffic_display_inc$dwd_traffic_error_inc$dwd_user_register_inc$dwd_user_login_inc$dwd_traffic_page_view_inc$dwd_traffic_start_inc$dwd_traffic_action_inc$dwd_interaction_test_exam_inc$dwd_interaction_watch_video_inc$dwd_traffic_log_watch_video_inc$dwd_trade_audition_order_full"
 esac

